import mysql from 'mysql2/promise';
import { Pool, PoolClient } from 'pg';
import Logger from './logger';
import sql, { empty, join, raw } from 'sql-template-tag';
import { PrismaClient } from '../../../prisma/generated/uctoo';
import { db_info, db_connection } from '../../../prisma/generated/uctoo';
import { parseDbUrl } from './utilsJS'

require('dotenv').config()
let dbConfig = parseDbUrl(process.env.DATABASE_URL)

let connection: db_connection = null;

async function loadDbInfo(databaseName: string): Promise<void> {
  Logger.info('loadDbInfo')
  const args = process.argv.slice(2); // 去掉数组中的前两个元素
  let dbName = databaseName ? databaseName : args[0];
  //优先从db_connection表中获取数据库连接信息
  const prisma = new PrismaClient()
  const connectionData = await prisma.db_connection.findMany({
    where: { database_name: dbName },
  })
  let pool;
  if (connectionData.length > 0) {
    connection = connectionData[0];
    pool = new Pool({
        user: connection.user,
        host: connection.host,
        password: connection.password,
        port: Number(connection.port),
        database: connection.database_name
      })
  } else {  //如果没有连接信息，则使用环境变量中的连接信息
      pool = new Pool({
        user: dbConfig.user,
        host: dbConfig.host,
        password: dbConfig.password,
        port: Number(dbConfig.port),
        database: dbName
      })
  }
  //连接postgresql数据库读取数据库信息
  if (connection.provider === 'postgresql') {
    console.log('postgresql');
    Logger.info(connectionData);
  
    let client: PoolClient
    try {
      //连接数据库
      let client = await pool.connect()
      // 查询是否存在数据库
      const result = await client.query(
        `SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('${dbName}')`
      )
      if (result.rowCount === 0) {
        // 不存在，获取数据库信息失败

        console.log(`load Database ${dbName} fail.`)
      } else {
        //存在，获取数据库信息


        //先清空db_info 表，todo: 支持数据库版本管理，命令行添加-f 参数，可清空已有数据。否则保留原有版本数据，以_prisma_migrations.id新建一批数据
        const deleteData = await prisma.db_info.deleteMany({
          where: { table_catalog: dbName },
        })

        const dbInfo = await client.query(`SELECT
          c.table_catalog,
          c.table_schema,
          c.table_name,
          c.column_name,
          c.ordinal_position,
          c.column_default,
          c.is_nullable,
          c.data_type,
          c.character_maximum_length,
          pgd.description AS column_comment
          FROM
          information_schema.columns c
          LEFT JOIN
          pg_catalog.pg_class cl ON (
              c.table_name = cl.relname
          )
          LEFT JOIN
          pg_catalog.pg_namespace n ON (
              cl.relnamespace = n.oid
          )
          LEFT JOIN
          pg_catalog.pg_attribute a ON (
              n.nspname = c.table_schema AND
              cl.oid = a.attrelid AND
              c.column_name = a.attname
          )
          LEFT JOIN
          pg_catalog.pg_description pgd ON (
              pgd.objoid = cl.oid AND
              pgd.objsubid = a.attnum
          )
          WHERE
          c.table_schema NOT IN ('pg_catalog', 'information_schema')
          ORDER BY
          c.table_schema,
          c.table_name,
          c.ordinal_position;`)


        //保存到db_info表，可作为代码生成的数据源使用
        try {
          const createMany = await prisma.db_info.createMany({
            data: dbInfo.rows,
            skipDuplicates: true, // 跳过 重复项
          })

          console.log(`load Database ${dbInfo.rows.length} column success.`)
        } catch (e) {
          console.error(e)
        }
      }
    } finally {
      // 释放客户端连接
      if (client) {
        client.release()
      }
    }
  }
  //todo:连接mysql数据库读取数据库信息
  if ( connection.provider === 'mysql') {
    console.log('mysql');

    let mysqlClient;
    if (connectionData.length > 0) {

      //连接数据库
      mysqlClient = await mysql.createConnection({
        host: connection.host,
        user: connection.user,
        password: connection.password,
        database: connection.database_name,
        port: connection.port ? Number(connection.port) : 3306
      });
    } else {  //如果没有连接信息，则使用环境变量中的连接信息
      mysqlClient = await mysql.createConnection({
        host: dbConfig.host,
        user: dbConfig.user,
        password: dbConfig.password,
        database: dbName,
        port: dbConfig.port ? Number(dbConfig.port) : 3306
      });
    }
    try {

      // 查询数据库是否存在
      const [dbRows] = await mysqlClient.execute(
        `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?`,
        [dbName]
      );

      if (dbRows.length === 0) {
        console.log(`load Database ${dbName} fail.`);
      } else {
        const prisma = new PrismaClient();
        // 清空db_info表
        await prisma.db_info.deleteMany({
          where: { table_catalog: dbName }
        });

        // MySQL查询表结构信息，mysql中的table_schema和table_catalog与对应postgresql中的对换了
        const [dbInfo] = await mysqlClient.execute(`
        SELECT 
          TABLE_CATALOG AS table_schema,
          TABLE_SCHEMA AS table_catalog,
          TABLE_NAME AS table_name,
          COLUMN_NAME AS column_name,
          ORDINAL_POSITION AS ordinal_position,
          COLUMN_DEFAULT AS column_default,
          IS_NULLABLE AS is_nullable,
          DATA_TYPE AS data_type,
          CHARACTER_MAXIMUM_LENGTH AS character_maximum_length,
          COLUMN_COMMENT AS column_comment
        FROM 
          INFORMATION_SCHEMA.COLUMNS
        WHERE 
          TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
        ORDER BY 
          TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
      `);

        // 保存到db_info表
        try {
          await prisma.db_info.createMany({
            data: dbInfo,
            skipDuplicates: true
          });
          console.log(`load Database ${dbInfo.length} column success.`);
        } catch (e) {
          console.error(e);
        }
      }
    } finally {
      if (mysqlClient) {
        await mysqlClient.end();
      }
    }
  }

}

// 使用函数检查并获取数据库信息
const args = process.argv.slice(2);
if (args.length > 0) {
  loadDbInfo(args[0]).then(() => {
    console.log('数据库信息加载完成，程序将退出');
    process.exit(0); // 显式退出进程
  }).catch((e) => {
    console.error(e.stack);
    process.exit(1); // 出错时也显式退出进程
  })
} else {
  console.error('请提供数据库名称参数');
  process.exit(1); // 提供参数错误时也显式退出进程
}